﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data;

namespace MasMembers.DAL
{
    class EvaluationEntity
    {
       
        public Int32 e_Id { get; set; }
        public Int32 MemSerialInGroup { get; set; }
        public Int32 Mem_Id { get; set; }
        public Int32 e_Modified_By { get; set; }
        public String e_Date_Of_Join { get; set; }
        public String e_Closes_Level { get; set; }
        
    }

    class Evaluation
    {
        private static string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='MasMembers.accdb';Jet OLEDB:Database Password=Yarabe11";
        private static String cmdText = "";

        public static bool Add(EvaluationEntity s)
        {
            bool flag = true;

            cmdText = "Insert into Evaluation (Mem_Id,Modified_By,Date_Of_Join,Closes_Level,MemSerialInGroup) values (?,?,?,?,?)";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);


            cmd.Parameters.AddWithValue("@Mem_Id", s.Mem_Id);
            cmd.Parameters.AddWithValue("@Modified_By", s.e_Modified_By);
            cmd.Parameters.AddWithValue("@Date_Of_Join", s.e_Date_Of_Join);
            cmd.Parameters.AddWithValue("@Closes_Level", s.e_Closes_Level);
            cmd.Parameters.AddWithValue("@MemSerialInGroup", s.MemSerialInGroup);

            try
            {
                Con.Open();
                int Num = cmd.ExecuteNonQuery();
                if (Num == 0)
                    flag = false;
            }

            catch (Exception ex)
            {
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;


        }
       
        public static bool Update(EvaluationEntity s)
        {
            bool flag = true;

            cmdText = "Update Evaluation set Mem_Id=?, Modified_By=?, Date_Of_Join=?, Closes_Level=?, MemSerialInGroup=? WHERE Id= ? ";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);
            cmd.Parameters.AddWithValue("@Mem_Id", s.Mem_Id);
            cmd.Parameters.AddWithValue("@Modified_By", s.e_Modified_By);
            cmd.Parameters.AddWithValue("@Date_Of_Join", s.e_Date_Of_Join);
            cmd.Parameters.AddWithValue("@Closes_Level", s.e_Closes_Level);
            cmd.Parameters.AddWithValue("@MemSerialInGroup", s.MemSerialInGroup);
            cmd.Parameters.AddWithValue("@Id", s.e_Id);
          

            try
            {
                Con.Open();
                int num = cmd.ExecuteNonQuery();
                if (num == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;

        }

        public static bool Delete(Int32 s)
        {
            bool flag = true;

            cmdText = "Delete from Evaluation where Id = ?";

            OleDbConnection Con = new OleDbConnection(conString);
            OleDbCommand cmd = new OleDbCommand(cmdText, Con);

            cmd.Parameters.AddWithValue("@Id", s);

            try
            {
                Con.Open();
                int num = cmd.ExecuteNonQuery();
                if (num == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;
        }

        public static EvaluationEntity GetEvaluationByE_ID(Int32 Id)
        {
            {
                EvaluationEntity x = null;
                
                cmdText = "Select * from Evaluation where Mem_Id= ? ";

                OleDbConnection Con = new OleDbConnection(conString);
                OleDbCommand cmd = new OleDbCommand(cmdText, Con);

                cmd.Parameters.AddWithValue("@Id", Id);

                try
                {
                    Con.Open();

                    OleDbDataReader reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        x = FillEntityFromReader(reader);

                    }

                    reader.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
                }
                finally
                {
                    if (Con.State == ConnectionState.Open)
                        Con.Close();
                }

                return x;
            }
        }
        
  

        public static List<EvaluationEntity> GetAllEvaluations()
        {
            {
                EvaluationEntity x = null;
                List<EvaluationEntity> EvaluatList = new List<EvaluationEntity>();

                cmdText = "Select * from Evaluation   ";

                OleDbConnection Con = new OleDbConnection(conString);
                OleDbCommand cmd = new OleDbCommand(cmdText, Con);



                try
                {
                    Con.Open();

                    OleDbDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        x = FillEntityFromReader(reader);
                        EvaluatList.Add (x);
                    }

                    reader.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("خطأ عند تحديد كل البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
                }
                finally
                {
                    if (Con.State == ConnectionState.Open)
                        Con.Close();
                }

                return EvaluatList;
            }
        }

        private static EvaluationEntity FillEntityFromReader(OleDbDataReader reader)
        {
        
            EvaluationEntity x = new EvaluationEntity();
            x.e_Id = Int32.Parse(reader["Id"].ToString());
            if (reader["Mem_Id"] != null)
                x.Mem_Id =Int32.Parse( reader["Mem_Id"].ToString());
            if (reader["MemSerialInGroup"] != null)
                x.MemSerialInGroup = Int32.Parse(reader["MemSerialInGroup"].ToString());
            if (reader["Modified_By"] != null)
                x.e_Modified_By = Int32.Parse( reader["Modified_By"].ToString());
            if ((reader["Date_Of_Join"] != null))
                x.e_Date_Of_Join = reader["Date_Of_Join"].ToString();
            if (reader["Closes_Level"] != null)
                x.e_Closes_Level = reader["Closes_Level"].ToString();
       
            return x;
        }
    }
    
    }

